package furstadminka.dbinterraction;

import furstadminka.data.DataPosition;
import furstadminka.utils.Preconditions;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Оболочка для удаления/модификации информации в БД
 *
 * @author Vit
 */
public class Inserter {
    /**
     * Стандартный логгер
     */
    private static final Logger logger = Logger.getLogger(Inserter.class);

    /**
     * Вводит новый элемент меню
     *
     * @param dataPosition Элемент меню
     */
    public static void addData(DataPosition dataPosition) {
        Preconditions.notNull(dataPosition);
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String quo = "'";
            String com = ",";
            String insert = "INSERT INTO positionlist(versioncode,categoryname,name,textcontent,price,image) VALUES (" + quo + dataPosition.getVersionCode() + quo + com + quo + dataPosition.getCategoryName() + quo + com + quo + dataPosition.getName() + quo + com + quo + dataPosition.getTextContent() + quo + com + dataPosition.getPrice() + com + quo + dataPosition.getImage() + quo + ")";
            statement.executeUpdate(insert);
            connection.close();
        } catch (ClassNotFoundException e) {
            logger.error("Class from Settings not found " + e);
        } catch (SQLException e) {
            logger.error("Problem with SQL " + e);
        }
    }

    /**
     * Удаляет элемент из меню
     *
     * @param id ИД элемента меню
     */
    public static void deleteRow(String id) {
        Preconditions.notBlank(id);
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String quo = "'";
            String delete = "DELETE FROM positionlist WHERE versioncode=" + quo + id + quo;
            statement.executeUpdate(delete);
            connection.close();
        } catch (ClassNotFoundException e) {
            logger.error("Class from Settings not found " + e);
        } catch (SQLException e) {
            logger.error("Problem with SQL " + e);
        }
    }

    /**
     * Добавляет строку в БД, потом обновляет его номер версии
     *
     * @param dataPosition   Объект с данными элемента
     * @param newVersionCode Новый номер версии
     */
    public static void updateRow(DataPosition dataPosition, String newVersionCode) {
        Preconditions.notNull(dataPosition);
        Preconditions.notBlank(newVersionCode);
        try {
            Class.forName(Settings.getDriverName());
            String url = "jdbc:mysql://" + Settings.getServerName() + ":" + Settings.getServerPort() + "/" + Settings.getMydatabase();
            Connection connection = (Connection) DriverManager.getConnection(url, Settings.getUsername(), Settings.getPassword());
            Statement statement = (Statement) connection.createStatement();
            String quo = "'";
            String com = ",";
            String update = "UPDATE positionlist SET categoryname=" + quo + dataPosition.getCategoryName() + quo + com + "name=" + quo + dataPosition.getName() + quo + com + "textcontent=" + quo + dataPosition.getTextContent() + quo + com + "price=" + dataPosition.getPrice() + com + "image=" + quo + dataPosition.getImage() + quo + " WHERE versioncode=" + quo + dataPosition.getVersionCode() + quo;
            String newVersionCodeQuery = "UPDATE positionlist SET versioncode=" + quo + newVersionCode + quo + " WHERE versioncode=" + quo + dataPosition.getVersionCode() + quo;
            statement.executeUpdate(update);
            statement.executeUpdate(newVersionCodeQuery);
            connection.close();
        } catch (ClassNotFoundException e) {
            logger.error("Class from Settings not found " + e);
        } catch (SQLException e) {
            logger.error("Problem with SQL " + e);
        }
    }
}
